using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using System.Collections.Generic;

/// <summary>
/// Summary description for HDDatabase
/// </summary>
public class HDDatabase
{
    private string strConString = "";
    private string strErrMessage = "";

    public HDDatabase(string strConString)
    {
        //strConString = @"Data Source=chen-lptp-001;Initial Catalog=ngmaster;User Id=sa;Password=sa";
        this.strConString = strConString;
    }

    public HDDatabase()
    {
        if (ReadAppFrameINI())
        {
            if (HttpContext.Current.Session["dbcon"] != null)
            {
                this.strConString = HttpContext.Current.Session["dbcon"].ToString();
            }
            else
            {
                if (HttpContext.Current.Session["servercon"].ToString() != "")
                {
                    this.strConString = HttpContext.Current.Session["servercon"].ToString();
                    HttpContext.Current.Session["dbcon"] = HttpContext.Current.Session["servercon"];
                }
            }
            if (this.strConString == "")
            {
                if (HttpContext.Current.Session["servercon"].ToString() != "")
                {
                    this.strConString = HttpContext.Current.Session["servercon"].ToString();
                    HttpContext.Current.Session["dbcon"] = HttpContext.Current.Session["servercon"];
                }
            }
        }
        else
        {
            if (HttpContext.Current.Session["dbcon"] != null)
            {
                this.strConString = HttpContext.Current.Session["dbcon"].ToString();
            }
            else
            {

                this.strConString = @"Data Source=csnindsrv010;Initial Catalog=ngmodel;User Id=sa;Password=sa"; //this.strConString = @"Data Source=chen-lptp-001;Initial Catalog=ngmodel;User Id=sa;Password=sa";//changed by Jeya Prabha on 22-Aug-2007
                HttpContext.Current.Session["servercon"] = this.strConString;
            }
            if (this.strConString == "")
            {
                this.strConString = @"Data Source=csnindsrv010;Initial Catalog=ngmodel;User Id=sa;Password=sa";
                HttpContext.Current.Session["servercon"] = this.strConString;
            }
        }
    }

    public SqlConnection GetConnection()
    {
        SqlConnection conObj = new SqlConnection(strConString);
        return conObj;
    }

    public SqlConnection GetConnection(string ConVariable)
    {
        SqlConnection conObj = new SqlConnection();
        if (HttpContext.Current.Session[ConVariable] != null)
            if (HttpContext.Current.Session[ConVariable].ToString() != "")
                conObj = new SqlConnection(HttpContext.Current.Session[ConVariable].ToString());
        return conObj;
    }

    public string GetColumnValue(string strTableName, string strColName, string strWhere)
    {
        string strValue = "";
        string strSql = "select [" + strColName.Replace("'", "''") + "] from [" + strTableName.Replace("'", "''") + "]";
        if (strWhere != "")
        {
            strSql += " where " + strWhere;
        }
        strValue = GetColumnValue(strSql);
        return strValue;
    }

    public string GetColumnValue(string strSql)
    {
        string strValue = "";
        DataTable dtaObj = GetDataTable(strSql);
        if (dtaObj.Rows.Count > 0)
        {
            strValue = dtaObj.Rows[0][0].ToString();
        }
        return strValue;
    }

    public string GetColumnValue(string strSql, bool filter)
    {
        string strValue = "";
        DataTable dtaObj = GetDataTable(strSql, filter);
        if (dtaObj.Rows.Count > 0)
        {
            strValue = dtaObj.Rows[0][0].ToString();
        }
        return strValue;
    }

    public DataRow GetTableRow(string strTableName, string strColName, string strWhere)
    {
        string strSql = "select [" + strColName.Replace("'", "''") + "] from [" + strTableName.Replace("'", "''") + "]";
        if (strWhere != "")
        {
            strSql += " where " + strWhere;
        }
        return GetTableRow(strSql);
    }

    public DataRow GetTableRow(string strSql)
    {
        DataRow droObj = null;
        DataTable dtaObj = GetDataTable(strSql);
        if (dtaObj.Rows.Count > 0)
        {
            droObj = dtaObj.Rows[0];
        }
        return droObj;
    }

    public DataTable GetTable(string strTableName, string strColName, string strWhere)
    {
        string strSql = "select [" + strColName.Replace("'", "''") + "] from [" + strTableName.Replace("'", "''") + "]";
        if (strWhere != "")
        {
            strSql += " where " + strWhere;
        }
        return GetDataTable(strSql);
    }

    public DataTable GetDataTable(string strSql)
    {
        return GetDataTable(strSql, "");
    }

    public DataTable GetDataTable(string strSql, bool filter)
    {
        return GetDataTable(strSql, "", filter);
    }

    public DataTable GetDataTable(string strSql, bool filter, bool isSearch)
    {
        return GetDataTable(strSql, "", filter, isSearch);
    }

    public DataTable GetDataTable(string strSql, string strTargetTableName)
    {
        DataTable dt = new DataTable();
        DataSet dseObj = GetDataset(strSql);
        if (dseObj.Tables.Count > 0)
        {
            if (strTargetTableName != "")
            {
                dseObj.Tables[0].TableName = strTargetTableName;
            }
            dt = dseObj.Tables[0];
        }
        return dt;
    }

    public DataTable GetDataTable(string strSql, string strTargetTableName, bool filter)
    {
        DataTable dt = new DataTable();
        DataSet dseObj = GetDataset(strSql, filter);
        if (dseObj.Tables.Count > 0)
        {
            if (strTargetTableName != "")
            {
                dseObj.Tables[0].TableName = strTargetTableName;
            }
            dt = dseObj.Tables[0];
        }
        return dt;
    }

    public DataTable GetDataTable(string strSql, string strTargetTableName, bool filter, bool isSearch)
    {
        DataTable dt = new DataTable();
        DataSet dseObj = GetDataset(strSql, filter, isSearch);
        if (dseObj.Tables.Count > 0)
        {
            if (strTargetTableName != "")
            {
                dseObj.Tables[0].TableName = strTargetTableName;
            }
            dt = dseObj.Tables[0];
        }
        return dt;
    }

    public DataSet GetDataset(string strSql)
    {
        DataSet dseObj = new DataSet();
        //string reqPath = "";//ssks
        SqlCommand cmdObj = null;
        try
        {
            //reqPath = HttpContext.Current.Request.Path;//ssks
            //LogWriter.WriteLog(strSql); Commented by srivatsan on July 9 2009
            cmdObj = new SqlCommand(strSql, GetConnection());
            SqlDataAdapter sdaObj = new SqlDataAdapter(cmdObj);
            sdaObj.Fill(dseObj);
        }
        catch (SqlException ex)
        {
            strErrMessage = ex.Message;
            LogWriter.WriteLog(strErrMessage);
            LogWriter.WriteLog("Error SQL : " + strSql);
            if (ex.Number == 18456)
            {
                HttpContext.Current.Response.Redirect("Error.aspx?msg=" + "Login failed.");
                //HttpContext.Current.Response.Redirect("Error.aspx?msg=" + ex.Message);
            }
        }
        catch (Exception ex)
        {
            strErrMessage = ex.Message;
            //if (reqPath.ToLower().IndexOf("default.aspx") > -1 || reqPath.ToLower().IndexOf("welcome.aspx") > -1) fillDataOnError(ref dseObj, ex.Message, this.strConString, strSql);//ssks
            LogWriter.WriteLog(strErrMessage);
            LogWriter.WriteLog("Error SQL : " + strSql);
            //HttpContext.Current.Response.Redirect("Error.aspx");
        }
        finally // Srivatsan Jun 9 2010
        {
            if (cmdObj != null)
            {
                if (cmdObj.Connection != null)
                {
                    if (cmdObj.Connection.State != ConnectionState.Closed)
                    {
                        cmdObj.Connection.Close();
                    }
                    cmdObj.Connection = null;
                }
                cmdObj = null;
            }
        }
        return dseObj;
    }

    public DataSet GetDataset(string strSql, bool filter)
    {
        DataSet dseObj = new DataSet();
        try
        {
            security objSec = new security();
            if (objSec.allRightsForNGMODEL())
                filter = false;
            objSec = null;
            if (filter == true)
            {
                if (HttpContext.Current.Request.QueryString["WF"] != null) // check for dynamic screen loading from work item screen -- no filter to be applied
                {
                    if (HttpContext.Current.Request.QueryString["WF"].ToString() == "1")
                    {
                        dseObj = GetDataset(strSql);
                    }
                    else
                    {
                        SecurityFilter SF = new SecurityFilter();
                        dseObj = SF.GetFilterDataset(strSql);
                    }
                }
                else
                {
                    SecurityFilter SF = new SecurityFilter();
                    dseObj = SF.GetFilterDataset(strSql);
                }
            }
            else
            {
                dseObj = GetDataset(strSql);
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in GetDataset function : " + e.Message);
        }
        return dseObj;
    }

    public DataSet GetDataset(string strSql, bool filter, bool isSearch)
    {
        DataSet dseObj = new DataSet();
        try
        {
            security objSec = new security();
            if (objSec.allRightsForNGMODEL())
                filter = false;
            objSec = null;
            if (filter == true)
            {
                if (HttpContext.Current.Request.QueryString["WF"] != null) // check for dynamic screen loading from work item screen -- no filter to be applied
                {
                    if (HttpContext.Current.Request.QueryString["WF"].ToString() == "1")
                    {
                        dseObj = GetDataset(strSql);
                    }
                    else
                    {
                        SecurityFilter SF = new SecurityFilter();
                        dseObj = SF.GetFilterDataset(strSql);
                    }
                }
                else
                {
                    SecurityFilter SF = new SecurityFilter();
                    dseObj = SF.GetFilterDataset(strSql,true);
                }
            }
            else
            {
                dseObj = GetDataset(strSql);
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in GetDataset function : " + e.Message);
        }
        return dseObj;
    }

    public string GetQuery(string strSql)
    {
        try
        {
            if (strSql.Contains("*"))
            {
                return strSql;
            }
            else
            {
                //strSql = strSql.Replace(" from ", ", created_by as af_c_by from ");
                // Added by srivatsan on June 15 2009 for owner id field
                strSql = strSql.Replace(" from ", ", owner_id as af_c_by from ");
                return strSql;
            }
            
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in GetQuery function : " + e.Message + ", " + strSql);
        }
        return strSql;
    }

    public DataSet FilterDataset(DataSet dseObj)
    {
        try
        {
            string sRol_af_id = GetColumnValue("select role_id from sy_usr_rol where user_id='" + HttpContext.Current.Session["user_afrowid"].ToString() + "'");
            //string sRol_id = GetColumnValue("select role_id from sy_rol where af_row_id='" + sRol_af_id + "'");

            string safadmin_row_id = GetColumnValue("select af_row_id from sy_usr where user_id='afadmin'");

            DataRow[] filterRowArray;
            DataSet dseFilter = new DataSet();
            dseFilter = dseObj.Clone();

            //if (sRol_id == "afadmin:accessrights")
            if (sRol_af_id == "e7029dafbe379f85") // Added by 'Imran Gani' on 08-May2014, to avoid role_id
            {
                dseObj.Tables[0].Columns.Remove("af_c_by"); 
                return dseObj;
            }
            else
            {
                filterRowArray = dseObj.Tables[0].Select("af_c_by <> '" + safadmin_row_id + "' or af_c_by IS NULL");

                foreach (DataRow row in filterRowArray)
                {
                    dseFilter.Tables[0].ImportRow(row);
                }

                dseFilter.Tables[0].Columns.Remove("af_c_by"); 
                return dseFilter;
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in FilterDataset function : " + e.Message);
            return dseObj;
        }
    }

    public void fillDataOnError(ref DataSet ds, string error, string connectionString, string strSql)
    {
        SqlCommand cmdObj = null;
        try
        {
            if (error.ToLower().IndexOf("invalid object name") > -1)
            {
                this.strConString = connectionString.Substring(0, connectionString.IndexOf(";")) + ";Initial Catalog=ngmodel;User Id=sa;Password=sa";

                //LogWriter.WriteLog(strSql); Commented by srivatsan on July 9 2009
                cmdObj = new SqlCommand(strSql, GetConnection());
                SqlDataAdapter sdaObj = new SqlDataAdapter(cmdObj);
                sdaObj.Fill(ds);
                this.strConString = connectionString;
            }
        }
        catch (Exception e)
        {
            strErrMessage = e.Message;
            LogWriter.WriteLog(strErrMessage);
            LogWriter.WriteLog("Error SQL : " + strSql);
        }
        finally // Srivatsan Jun 9 2010
        {
            if (cmdObj != null)
            {
                if (cmdObj.Connection != null)
                {
                    if (cmdObj.Connection.State != ConnectionState.Closed)
                    {
                        cmdObj.Connection.Close();
                    }
                    cmdObj.Connection = null;
                }
                cmdObj = null;
            }
        }
    }

    public bool isDataRecordExists(string strSql)
    {
        DataSet dseObj = new DataSet();
        bool blnRecordExists = false;
        SqlCommand cmdObj = null;
        try
        {
            //LogWriter.WriteLog(strSql); Commented by srivatsan on July 9 2009
            cmdObj = new SqlCommand(strSql, GetConnection());
            SqlDataAdapter sdaObj = new SqlDataAdapter(cmdObj);
            sdaObj.Fill(dseObj);
            if (dseObj.Tables[0].Rows.Count > 0)
            {
                blnRecordExists = true;
            }
        }
        catch (Exception ex)
        {
            strErrMessage = ex.Message;
            LogWriter.WriteLog(strErrMessage);
            LogWriter.WriteLog("Error SQL : " + strSql);
            blnRecordExists = false;
        }
        finally // Srivatsan Jun 9 2010
        {
            if (cmdObj != null)
            {
                if (cmdObj.Connection != null)
                {
                    if (cmdObj.Connection.State != ConnectionState.Closed)
                    {
                        cmdObj.Connection.Close();
                    }
                    cmdObj.Connection = null;
                }
                cmdObj = null;
            }
        }
        return blnRecordExists;
    }

    public string getAcrMemValue(string strAcrRowId, string strAcrMemId)
    {
        //string strSql = "select af_row_id from sy_acr where acr_id = '" + strAcrId.Replace("'", "''") + "'";
        //string sAF_ROW_ID = GetColumnValue(strSql);
        string strSql = "select mem_value1 from sy_acr_mem where acr_id='" + strAcrRowId + "' and mem_id='" + strAcrMemId.Replace("'", "''") + "'";
        return GetColumnValue(strSql);
    }

    public bool ExecuteTransaction(ArrayList arlCmdObj)
    {
        return ExecuteTransaction(arlCmdObj, "", null, null);
    }

    private bool applyEventChanges(ArrayList arlCmdObj, string strOperation, DataSet dsePageInfo, Hashtable hsValues, ref DataSet dseInputInfo)
    {
        bool bReturn = true;
        try
        {
            string sTemp = dsePageInfo.Tables["Page"].Rows[0]["multiple_recs_page"].ToString();
            bool bMultiplpeRecords = false;
            if (sTemp != "")
                bMultiplpeRecords = Convert.ToBoolean(sTemp);

            foreach (string sControlId in hsValues.Keys)
            {
                string sObjectid = sControlId.Substring(1, sControlId.IndexOf("].[") - 1);
                string sObjectName = dsePageInfo.Tables["page"].Select("object_id = '" + sObjectid + "'")[0]["page_table_name"].ToString();
                string sAttribId = sControlId.Substring(sControlId.IndexOf("].[") + 3, sControlId.Length - (sControlId.IndexOf("].[") + 3) - 1);

                if (sAttribId.Contains("]_"))
                    sAttribId = sAttribId.Substring(0, sAttribId.IndexOf("]_"));

                for (int iCount = 0; iCount < arlCmdObj.Count; iCount++)
                {
                    SqlCommand objCommand = (SqlCommand)arlCmdObj[iCount];
                    if (objCommand.CommandText.Contains("[" + sObjectName + "]"))
                    {
                        if (bMultiplpeRecords)
                        {
                            if (objCommand.Parameters.Contains("@" + sAttribId))
                            {
                                if (hsValues.Contains(sControlId + "_" + iCount.ToString()))
                                {
                                    objCommand.Parameters["@" + sAttribId].Value = hsValues[sControlId + "_" + iCount.ToString()].ToString();
                                    dseInputInfo.Tables[0].Rows[iCount][sAttribId] = hsValues[sControlId + "_" + iCount.ToString()].ToString();
                                }
                                else
                                {
                                    objCommand.Parameters["@" + sAttribId].Value = hsValues[sControlId].ToString();
                                    dseInputInfo.Tables[0].Rows[iCount][sAttribId] = hsValues[sControlId].ToString();
                                }
                            }
                        }
                        else
                        {
                            if (objCommand.Parameters.Contains("@" + sAttribId))
                            {
                                objCommand.Parameters["@" + sAttribId].Value = hsValues[sControlId].ToString();
                                dseInputInfo.Tables[0].Rows[0][sAttribId] = hsValues[sControlId].ToString();
                            }
                        }

                        arlCmdObj[iCount] = objCommand;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("applyEventChanges : Error : " + ex.Message);
            bReturn = false;
        }

        return bReturn;
    }

    private bool applyPageEvents(ArrayList arlCmdObj, string strOperation, DataSet dsePageInfo, DataSet dseInputInfo)
    {
        bool bReturn = true;
        try
        {
            Hashtable hsValues = new Hashtable();
            if (dsePageInfo != null)
            {
                PageInfoLoad PIL = new PageInfoLoad();
                foreach (DataRow dr in dsePageInfo.Tables["Page"].Rows)
                {
                    DataSet dsPageEvents = PIL.pageEventHandler(dr["page_id"].ToString());

                    if (dsPageEvents != null)
                    {
                        if (dsPageEvents.Tables.Count > 0)
                        {
                            if (dsPageEvents.Tables[0].Rows.Count != 0)
                            {
                                string sEventCode = "";
                                switch (strOperation)
                                {
                                    case "INSERT":
                                        sEventCode = "PS";
                                        break;
                                    case "UPDATE":
                                        sEventCode = "PU";
                                        break;
                                    case "AFTER_INSERT":
                                        sEventCode = "PAS";
                                        break;
                                    case "AFTER_UPDATE":
                                        sEventCode = "PAU";
                                        break;
                                }

                                if (sEventCode != "")
                                {
                                    if (PIL.triggerEvent(dsPageEvents, sEventCode, ref hsValues, ref dseInputInfo, null) == false)
                                        return false;
                                    if (!strOperation.Contains("AFTER_"))
                                    {
                                        bool bApplyChanges = applyEventChanges(arlCmdObj, strOperation, dsePageInfo, hsValues, ref dseInputInfo);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("applyPageEvents : Error : " + ex.Message);
            strErrMessage = ex.Message;
            bReturn = false;
        }

        return bReturn;
    }

    private String checkForUserDeletion(String SQL)
    {
        String sReturn = "";
        if (SQL.ToLower().StartsWith("delete "))
        {
            if (SQL.ToLower().Contains("[sy_usr]"))
            {
                sReturn = SQL.Replace("[sy_usr]", "[ngcompanyuser]");
            }
        }
        return sReturn;
    }

    private bool deleteUserFromNG(List<String> SQL)
    {
        bool bReturn = false;
        SqlConnection sqlConObj = null;
        SqlCommand sqlCommandObj = null;
        try
        {
            sqlConObj = GetConnection("servercon");
            sqlConObj.Open();

            foreach (String sTemp in SQL)
            {
                if (sTemp != "")
                {
                    sqlCommandObj = new SqlCommand(sTemp, sqlConObj);
                    int iRecs = sqlCommandObj.ExecuteNonQuery();
                }
            }
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("deleteUserFromNG : Error : " + Ex.Message);
        }
        finally
        {
            if (sqlConObj != null)
            {
                if (sqlConObj.State != ConnectionState.Closed)
                {
                    sqlConObj.Close();
                }
                sqlConObj = null;
            }
            if (sqlCommandObj != null)
            {
                sqlCommandObj.Dispose();
                sqlCommandObj = null;
            }
        }
        return bReturn;
    }

    public bool ExecuteTransaction(ArrayList arlCmdObj, string strOperation, DataSet dsePageInfo, DataSet dseInputInfo)
    {
        bool retrn = false;
        try
        {
            bool deadLockError = false;            
            retrn = ExecuteTransaction(arlCmdObj, strOperation, dsePageInfo, dseInputInfo, ref deadLockError);
            if (deadLockError==true)
            {
                LogWriter.WriteLog("Re-attempting deadlock query");
                System.Threading.Thread.Sleep(2000);
                LogWriter.WriteLog("Re-attempting deadlock query - after delay");
                retrn = ExecuteTransaction(arlCmdObj, strOperation, dsePageInfo, dseInputInfo, ref deadLockError);
            }            
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("ExecuteTransaction: Error : " + ee.Message);
        }
        return retrn;
    }

    private bool ExecuteTransaction(ArrayList arlCmdObj, string strOperation, DataSet dsePageInfo, DataSet dseInputInfo,ref bool deadLockError)
    {
        bool blnStatus = true;
        string strSql = "";
        SqlTransaction sqlTrans = null;
        int count =0;
        SqlConnection conObj = null;
        try
        {
            #region MyRegion
            //Hashtable hsValues = new Hashtable();
            //if (dsePageInfo != null)
            //{
            //    PageInfoLoad PIL = new PageInfoLoad();
            //    foreach (DataRow dr in dsePageInfo.Tables["Page"].Rows)
            //    {
            //        DataSet dsPageEvents = PIL.pageEventHandler(dr["page_id"].ToString());

            //        if (dsPageEvents != null)
            //        {
            //            if (dsPageEvents.Tables.Count > 0)
            //            {
            //                string sEventCode = "";
            //                if (strOperation == "INSERT")
            //                    sEventCode = "PS";
            //                else if (strOperation == "UPDATE")
            //                    sEventCode = "PU";
            //                if (sEventCode != "")
            //                    if (PIL.triggerEvent(dsPageEvents, sEventCode, ref hsValues, ref dseInputInfo) == false)
            //                        return false;
            //                Hashtable hsTemp = new Hashtable();
            //                foreach (string sControlId in hsValues.Keys)
            //                {
            //                    string sObjectid = sControlId.Substring(1, sControlId.IndexOf("].[") - 1);
            //                    string sObjectName = dsePageInfo.Tables["page"].Select("object_id = '" + sObjectid + "'")[0]["page_table_name"].ToString();
            //                    string sAttribId = sControlId.Substring(sControlId.IndexOf("].[") + 3, sControlId.Length - (sControlId.IndexOf("].[") + 3) - 1);

            //                    for (int iCount = 0; iCount < arlCmdObj.Count; iCount++)
            //                    {
            //                        SqlCommand objCommand = (SqlCommand)arlCmdObj[iCount];
            //                        if (objCommand.CommandText.Contains("[" + sObjectName + "]"))
            //                        {
            //                            if (objCommand.Parameters.Contains("@" + sAttribId))
            //                                objCommand.Parameters["@" + sAttribId].Value = hsValues[sControlId];

            //                            arlCmdObj[iCount] = objCommand;
            //                        }
            //                    }
            //                }
            //            }
            //        }
            //    }
            //} 
            #endregion

            bool bPageEvents = applyPageEvents(arlCmdObj, strOperation, dsePageInfo, dseInputInfo);

            if (bPageEvents)
            {
                conObj = GetConnection();
                conObj.Open();
                //LogWriter.WriteLog("Transaction Started");Commented by srivatsan on July 9 2009
                sqlTrans = conObj.BeginTransaction();
                List<String> sNGCompanyUserDeletion = new List<String>();
                int intRecsAffect = -1;
                for (int i = 0; i < arlCmdObj.Count; i++)
                {
                    SqlCommand cmdObj = (SqlCommand)arlCmdObj[i];
                    strSql = cmdObj.CommandText;

                    sNGCompanyUserDeletion.Add(checkForUserDeletion(strSql));
                    //LogWriter.WriteLog("Command going to execute: " + strSql);Commented by srivatsan on July 9 2009
                    //WriteParameters(cmdObj); Commented by srivatsan on July 9 2009
                    cmdObj.Connection = conObj;
                    cmdObj.Transaction = sqlTrans;
                    count = i;
                    intRecsAffect = cmdObj.ExecuteNonQuery();
                    //LogWriter.WriteLog("Record affected : " + intRecsAffect.ToString());Commented by srivatsan on July 9 2009
                }

                if (strOperation != "")
                {
                    // PageFunctionsCustom needs to initalized here only. Don't move this to class level scope. 
                    // To avoid the circular reference, declared here
                    PageFunctionsCustom PFC = new PageFunctionsCustom();
                    blnStatus = PFC.ExecuteCustomFunction(strOperation, dsePageInfo, dseInputInfo);
                    strErrMessage = PFC.ErrorMessage;
                }

                if (blnStatus == true)
                {
                    if (dsePageInfo != null)
                        if (dsePageInfo.Tables["page"].Select("object_id='usr'").Length == 1)
                        {
                            if (dseInputInfo != null)
                            {
                                if (HttpContext.Current.Session["user_afrowid"].ToString() == dseInputInfo.Tables["p_usr"].Rows[0]["af_row_id"].ToString())
                                {
                                    HttpContext.Current.Session["theme"] = dseInputInfo.Tables["p_usr"].Rows[0]["theme"].ToString();
                                    HttpContext.Current.Session["userid"] = dseInputInfo.Tables["p_usr"].Rows[0]["user_id"].ToString();
                                    HttpContext.Current.Session["userlocale"] = dseInputInfo.Tables["p_usr"].Rows[0]["locale"].ToString(); // Added by 'Imran Gani' on 28-Feb-2014, for Locale session updates
                                }
                            }
                            UsersinNGMODEL(strOperation, dseInputInfo);
                        }
                    sqlTrans.Commit();
                    //LogWriter.WriteLog("Transaction Commit. Success"); Commented by srivatsan on July 9 2009

                    deleteUserFromNG(sNGCompanyUserDeletion);
                    conObj.Close();
                    bPageEvents = applyPageEvents(arlCmdObj, "AFTER_" + strOperation, dsePageInfo, dseInputInfo);
                }
                else
                {
                    sqlTrans.Rollback();
                    //LogWriter.WriteLog("Transaction Rolled back"); Commented by srivatsan on July 9 2009
                }

                conObj.Close();
            }
            else
                blnStatus = bPageEvents;
        }
        catch (Exception ex)
        {
            blnStatus = false;
            strErrMessage = ex.Message;
            LogWriter.WriteLog("Error : " + strErrMessage);
            LogWriter.WriteLog("Query : " + arlCmdObj[count].ToString());
            deadLockError = ex.Message.ToLower().IndexOf("deadlock victim") > -1;
            try
            {
                sqlTrans.Rollback();
                LogWriter.WriteLog("Transaction Rollbacked");
            }
            catch (Exception ex2)
            {
                deadLockError = false;
                LogWriter.WriteLog("Error in Rollback. " + ex2.Message);
            }
        }
        finally
        {
            if (conObj != null)
            {
                if (conObj.State != ConnectionState.Closed)
                {
                    conObj.Close();
                }
                conObj = null;
            }
        }

        return blnStatus;
    }


    public int ExecuteCmdObj(SqlCommand cmdObj)
    {
        return ExecuteCmdObject(cmdObj, true);
    }
    private int ExecuteCmdObject(SqlCommand cmdObj,bool processAgainIfDeadlock)
    {
        if (cmdObj.CommandText.Trim() == "") return 0;
        int intRet = -1; bool done = false;
        while (!done)
        {
            try
            {
                //LogWriter.WriteLog("Command going to execute : " + cmdObj.CommandText); Commented by srivatsan on July 9 2009
                //WriteParameters(cmdObj); Commented by srivatsan on July 9 2009
                cmdObj.Connection = GetConnection();
                cmdObj.Connection.Open();
                intRet = cmdObj.ExecuteNonQuery();
                //LogWriter.WriteLog("Result : " + intRet.ToString()); Commented by srivatsan on July 9 2009
                //cmdObj.Connection.Close();
                done = true;
            }
            catch (Exception ex)
            {
                strErrMessage = ex.Message;
                LogWriter.WriteLog("Error : " + strErrMessage);
                LogWriter.WriteLog("Error Sql : " + cmdObj.CommandText);
                if (processAgainIfDeadlock == true)//handle deadlock
                {
                    if (ex.Message.ToLower().IndexOf("deadlock victim") > -1)//checking for deadlock
                    {
                        try
                        {
                            LogWriter.WriteLog("Re-attempting deadlock query");
                            System.Threading.Thread.Sleep(1000);
                            cmdObj.Connection.Open();
                            intRet = cmdObj.ExecuteNonQuery();
                            LogWriter.WriteLog("Result : " + intRet.ToString());
                            cmdObj.Connection.Close();
                            done = true;
                        }
                        catch (Exception exc)
                        {
                            done = true;
                            strErrMessage = exc.Message;
                            LogWriter.WriteLog("Error : " + strErrMessage);
                        }
                    }
                    else
                    {
                        done = true;
                    }
                }
                else
                {
                    done = true;
                }
            }
            finally
            {
                if (cmdObj != null)
                {
                    if (cmdObj.Connection != null)
                    {
                        if (cmdObj.Connection.State != ConnectionState.Closed)
                        {
                            cmdObj.Connection.Close();
                        }
                        cmdObj.Connection = null;
                    }
                }
            }
        }
        return intRet;
    }
    public int ExecuteCmdObj(SqlCommand cmdObj, bool rollBackEnabled)
    {
        if (!rollBackEnabled) return ExecuteCmdObj(cmdObj);
        if (cmdObj.CommandText.Trim() == "") return 0;
        int intRet = -1;SqlTransaction st = null;// = new SqlTransaction();
        try
        {
            //LogWriter.WriteLog("Command going to execute : " + cmdObj.CommandText); Commented by srivatsan on July 9 2009
            //WriteParameters(cmdObj); Commented by srivatsan on July 9 2009
            cmdObj.Connection = GetConnection();
            cmdObj.Connection.Open();
            st = cmdObj.Connection.BeginTransaction();
            cmdObj.Transaction = st;
            intRet = cmdObj.ExecuteNonQuery();
            LogWriter.WriteLog("Result : " + intRet.ToString());
            st.Commit();
            //cmdObj.Connection.Close();
        }
        catch (Exception ex)
        {
            try
            {
                if (st != null) st.Rollback();
            }
            catch (Exception e) { }
            strErrMessage = ex.Message;
            LogWriter.WriteLog("Error : " + strErrMessage);
            LogWriter.WriteLog("Error Sql : " + cmdObj.CommandText);
        }
        finally
        {
            if(cmdObj !=null)
                if (cmdObj.Connection != null)
                {
                    if (cmdObj.Connection.State != ConnectionState.Closed)
                    {
                        cmdObj.Connection.Close();
                    }
                    cmdObj.Connection = null;
                }
        }
        return intRet;
    }
    private bool WriteParameters(SqlCommand cmdObj)
    {
        bool blnStatus = true;
        for (int i = 0; i < cmdObj.Parameters.Count; i++)
        {
            LogWriter.WriteLog("Param Name : " + cmdObj.Parameters[i].ParameterName.ToString() + "; Value : " + cmdObj.Parameters[i].Value.ToString());
        }
        return blnStatus;
    }

    public string ErrorMessage
    {
        get
        {
            return this.strErrMessage;
        }
        set
        {
            this.strErrMessage = value;
        }
    }

    public bool isTableColumnExists(string strTableName, string strColumnName)
    {
        bool blnTableColumnExists = false;
        string strSQL = "Select name from syscolumns where object_name(id) = '" + strTableName.Replace("'", "''") + "' and name='" + strColumnName.Replace("'", "''") + "'";
        blnTableColumnExists = isDataRecordExists(strSQL);
        return blnTableColumnExists;
    }

    public bool isValidConnection()
    {
        bool blnValidConnection = false;
        SqlConnection scon = GetConnection();
        try
        {
            scon.Open();
            blnValidConnection = true;
            //scon.Close();
        }
        catch (Exception e)
        {
            strErrMessage = e.Message;
            LogWriter.WriteLog("Error : " + strErrMessage);
        }
        finally
        {
            if (scon != null)
            {
                if (scon.State != ConnectionState.Closed)
                {
                    scon.Close();
                }
                scon = null;
            }
        }
        return blnValidConnection;

    }

    private bool UsersinNGMODEL(string Operation, DataSet dsInput)
    {
        SqlConnection sqlConObj = GetConnection("servercon");
        ScreenReader SR = new ScreenReader();
        sqlConObj.Open();
        string sSql = "";
        string sUser_id = "";
        if (Operation.ToLower() != "delete")
        {
            sUser_id = dsInput.Tables["p_usr"].Rows[0]["user_id"].ToString();
            string sPassword = dsInput.Tables["p_usr"].Rows[0]["password"].ToString();

            if (Operation.ToLower() == "insert")
                sSql = "insert into ngcompanyuser(company_id,user_id,password,af_row_id,hint_question,hint_answer) values('" + HttpContext.Current.Session["company_id"].ToString().Replace("'", "''") + "','" + sUser_id.Replace("'", "''") + "','" + sPassword.Replace("'", "''") + "','" + dsInput.Tables["p_usr"].Rows[0]["af_row_id"].ToString() + "','" + dsInput.Tables["p_usr"].Rows[0]["hint_question"].ToString().Replace("'", "''") + "','" + dsInput.Tables["p_usr"].Rows[0]["hint_answer"].ToString().Replace("'", "''") + "')";
            //,'" + dsInput.Tables["p_usr"].Rows[0]["active"].ToString() + "'
            else if (Operation.ToLower() == "update")
                sSql = "update ngcompanyuser set user_id = '" + sUser_id.Replace("'", "''") + "' , password = '" + sPassword.Replace("'", "''") + "', hint_question = '" + dsInput.Tables["p_usr"].Rows[0]["hint_question"].ToString().Replace("'", "''") + "', hint_answer = '" + dsInput.Tables["p_usr"].Rows[0]["hint_answer"].ToString().Replace("'", "''") + "' where company_id='" + HttpContext.Current.Session["company_id"].ToString().Replace("'", "''") + "' and af_row_id ='" + dsInput.Tables["p_usr"].Rows[0]["af_row_id"].ToString() + "'";
            //,active ='" + dsInput.Tables["p_usr"].Rows[0]["active"].ToString() + "'
        }
        else
        {
            //sSql = "delete from ngcompanyuser where company_id='" + HttpContext.Current.Session["company_id"].ToString().Replace("'", "''") + "' and af_row_id ='" + HttpContext.Current.Request.QueryString["DPK"].ToString() + "'"; // Commented by srivatsan for delete issue
            if (HttpContext.Current.Session["DPK"] != null)
            {
                sSql = "delete from ngcompanyuser where company_id='" + HttpContext.Current.Session["company_id"].ToString().Replace("'", "''") + "' and af_row_id ='" + HttpContext.Current.Session["DPK"].ToString() + "'";
            }
        }
        SqlCommand sqlCommandObj = new SqlCommand(sSql, sqlConObj);
        int iRecs = sqlCommandObj.ExecuteNonQuery();
        //LogWriter.WriteLog("Record affected : " + iRecs.ToString()); Commented by srivatsan on July 9 2009
        sqlConObj.Close();
        sqlConObj.Dispose();
        sqlConObj = null;
        sqlCommandObj.Dispose();
        sqlCommandObj = null;
        return true;
    }

    private bool ReadAppFrameINI()
    {
        bool bReturn = true;
        try
        {
            if (HttpContext.Current.Session["servercon"] != null)
            {
                if (HttpContext.Current.Session["servercon"].ToString() != "")
                    return true;
            }

            if (bReturn && System.IO.File.Exists(HttpContext.Current.Server.MapPath(".") + "\\maxblox.ini"))
            {
                StreamReader sr = new StreamReader(HttpContext.Current.Server.MapPath(".") + "\\maxblox.ini");
                string sTemp = null;
                string sBlock = null;
                string sServerCon = null;
                do
                {
                    sTemp = sr.ReadLine();
                    if (sTemp != null)
                    {
                        if (sTemp.StartsWith("[") & sTemp.EndsWith("]"))
                            sBlock = sTemp;
                        switch (sBlock)
                        {
                            case "[SERVER]":
                                if (sTemp.IndexOf("=") != -1)
                                {
                                    switch (sTemp.Substring(0, sTemp.IndexOf("=")))
                                    {
                                        case "NAME":
                                            sServerCon = "Data Source=" + sTemp.Substring(sTemp.IndexOf("=") + 1) + ";";
                                            break;
                                        case "CATALOG":
                                            {
                                                string sCatalog = sTemp.Substring(sTemp.IndexOf("=") + 1);
                                                sServerCon = sServerCon + "Initial Catalog=" + sCatalog + ";";
                                                HttpContext.Current.Session["ServerCatalog"] = sCatalog;
                                            }
                                            break;
                                        case "USERNAME":
                                            sServerCon = sServerCon + "User Id=" + sTemp.Substring(sTemp.IndexOf("=") + 1) + ";";
                                            break;
                                        case "PASSWORD":
                                            sServerCon = sServerCon + "Password=" + security.decrypt(sTemp.Substring(sTemp.IndexOf("=") + 1)) + ";";
                                            break;
                                    }
                                }
                                break;
                        }
                    }
                } while (sTemp != null);

                sr.Close();
                sr.Dispose();
                sr = null;

                HttpContext.Current.Session["servercon"] = sServerCon;
            }
            else
                bReturn = false;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog(ex.Message);
            bReturn = false;
        }

        return bReturn;
    }

    //public object getSQL(string strSQLID)
    //{
    //    return 1;
    //}
    //public string getSQL(string strSQLID)
    //{
    //    return "";
    //}
    //public string getSQL(string strSQLID,string strParam1)
    //{

    //}
    //public string getSQL(string strSQLID, string strParam1, string strParam1)
    //{

    //}
}
